package com.base.common.utils.file;

import java.io.FileInputStream;
import java.io.InputStream;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

public class ExcelUtil {
	
	public static void main(String[] args) throws Exception {
//		ExcelUtil excel = new ExcelUtil();
//		excel.insertBrand("E:/logs/car/b1.xls");
	}
	
	
	public static void insertBrand(String path) throws Exception
	{
		InputStream is = new FileInputStream(path);
		HSSFWorkbook book = new HSSFWorkbook(is);
		for (int i = 0; i < book.getNumberOfSheets(); i++) {
			HSSFSheet sheet = book.getSheetAt(i);
			if (sheet == null) {
				continue;
			}
			for (int j = 0; j <= sheet.getLastRowNum(); j++) {
				HSSFRow row = sheet.getRow(j);
				if (row == null) {
					System.out.println("null-row");
					continue;
				}
				// id
				HSSFCell cell0 = row.getCell(0);
				if (cell0 == null) {
					System.out.println("null-cell");
				}
				cell0.setCellType(Cell.CELL_TYPE_STRING);
				String id = cell0.getStringCellValue();
				// parent
				HSSFCell cell1 = row.getCell(1);
				if (cell1 == null) {
					System.out.println("null-cell");
				}
				cell1.setCellType(Cell.CELL_TYPE_STRING);
				String parent = cell1.getStringCellValue();
				// name
				HSSFCell cell2 = row.getCell(2);
				if (cell2 == null) {
					System.out.println("null-cell");
				}
				cell2.setCellType(Cell.CELL_TYPE_STRING);
				String name = cell2.getStringCellValue();
				// code
				HSSFCell cell3 = row.getCell(3);
				if (cell3 == null) {
					System.out.println("null-cell");
				}
				cell3.setCellType(Cell.CELL_TYPE_STRING);
				String code = cell3.getStringCellValue();
				// type
				HSSFCell cell4 = row.getCell(4);
				if (cell4 == null) {
					System.out.println("null-cell");
				}
				cell4.setCellType(Cell.CELL_TYPE_STRING);
				String type = cell4.getStringCellValue();
				System.out.println(id+":"+parent+":"+name+":"+code+":"+type);
				
				if ("ID".equals(id)) 
				{
					System.out.println("=======================");
					continue;
				}				
			
			}
		}
	}
	
	public static void updateBrand(String path) throws Exception
	{
		InputStream is = new FileInputStream(path);
		HSSFWorkbook book = new HSSFWorkbook(is);
		for (int i = 0; i < book.getNumberOfSheets(); i++) {
			HSSFSheet sheet = book.getSheetAt(i);
			if (sheet == null) {
				continue;
			}
			for (int j = 0; j <= sheet.getLastRowNum(); j++) {
				HSSFRow row = sheet.getRow(j);
				if (row == null) {
					System.out.println("null-row");
					continue;
				}
				// id
				HSSFCell cell0 = row.getCell(0);
				if (cell0 == null) {
					System.out.println("null-cell");
				}
				cell0.setCellType(Cell.CELL_TYPE_STRING);
				String id = cell0.getStringCellValue();
				// name
				HSSFCell cell1 = row.getCell(1);
				if (cell1 == null) {
					System.out.println("null-cell");
				}
				cell1.setCellType(Cell.CELL_TYPE_STRING);
				String name = cell1.getStringCellValue();
				// dis
				HSSFCell cell2 = row.getCell(2);
				if (cell2 == null) {
					System.out.println("null-cell");
				}
				cell2.setCellType(Cell.CELL_TYPE_STRING);
				String dis = cell2.getStringCellValue();
				// type
				HSSFCell cell3 = row.getCell(3);
				if (cell3 == null) {
					System.out.println("null-cell");
				}
				cell3.setCellType(Cell.CELL_TYPE_STRING);
				String type = cell3.getStringCellValue();
				// oil
				HSSFCell cell4 = row.getCell(4);
				if (cell4 == null) {
					System.out.println("null-cell");
				}
				cell4.setCellType(Cell.CELL_TYPE_STRING);
				String oil = cell4.getStringCellValue();
				// tank
				HSSFCell cell5 = row.getCell(5);
				if (cell5 == null) {
					System.out.println("null-cell");
				}
				cell5.setCellType(Cell.CELL_TYPE_STRING);
				String tank = cell5.getStringCellValue();
				// min
				HSSFCell cell6 = row.getCell(6);
				if (cell6 == null) {
					System.out.println("null-cell");
				}
				cell6.setCellType(Cell.CELL_TYPE_STRING);
				String min = cell6.getStringCellValue();
				// max
				HSSFCell cell7 = row.getCell(7);
				if (cell7 == null) {
					System.out.println("null-cell");
				}
				cell7.setCellType(Cell.CELL_TYPE_STRING);
				String max = cell7.getStringCellValue();
				// getoil
				HSSFCell cell8 = row.getCell(8);
				if (cell8 == null) {
					System.out.println("null-cell");
				}
				cell8.setCellType(Cell.CELL_TYPE_STRING);
				String getoil = cell8.getStringCellValue();
				// getmile
				HSSFCell cell9 = row.getCell(9);
				if (cell9 == null) {
					System.out.println("null-cell");
				}
				cell9.setCellType(Cell.CELL_TYPE_STRING);
				String getmile = cell9.getStringCellValue();
				// useoil
				HSSFCell cell10 = row.getCell(10);
				if (cell10 == null) {
					System.out.println("null-cell");
				}
				cell10.setCellType(Cell.CELL_TYPE_STRING);
				String useoil = cell10.getStringCellValue();
				// avg
				HSSFCell cell11 = row.getCell(11);
				if (cell11 == null) {
					System.out.println("null-cell");
				}
				cell11.setCellType(Cell.CELL_TYPE_STRING);
				String avg = cell11.getStringCellValue();
				// nobd
				HSSFCell cell12 = row.getCell(12);
				if (cell12 == null) {
					System.out.println("null-cell");
				}
				cell12.setCellType(Cell.CELL_TYPE_STRING);	
				
//				
//				// String sql = "INSERT INTO `csh_vehicle_brand` (`id`, `name`, `code`, `parent`, `grade`, `status`, `add_time`, `admin`) VALUES (?,?,?,?,?,?,?,?)";
//				VehicleBrandModel brand = new VehicleBrandModel();
//				brand.set("id", id);
//				brand.set("name", name);
//				brand.set("code", code);
//				brand.set("parent", StringUtil.isNull(parent)?null:parent);
//				brand.set("grade", type);
//				brand.set("status", "0");
//				brand.set("add_time", new Date());
//				brand.set("admin", "0");
//				brand.save();
			}
		}
	}
}